//
//  HasSpecFmdbTool.m
//  HAODE
//
//  Created by LiYinDi on 16/5/14.
//  Copyright © 2016年 reabam.com. All rights reserved.
//

#import "HasSpecFmdbTool.h"
#define kUserDBNAME @"userDB.sqlite"
@implementation HasSpecFmdbTool
static FMDatabase *_fmdb;
+(void)initialize
{
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
    NSString *DBPath = [documentPath stringByAppendingPathComponent:kUserDBNAME];
    NSLog(@"DBPath///====%@",DBPath);
    _fmdb = [FMDatabase databaseWithPath:DBPath];
    if ([_fmdb open]) {
        
        BOOL result=[_fmdb executeUpdate:@"CREATE TABLE IF NOT EXISTS t_Spec (id integer PRIMARY KEY AUTOINCREMENT , userToken text NOT NULL , itemID text NOT NULL , itemCode text NOT NULL , colorSpecID text NOT NULL , sizeSpecID text NOT NULL , specName text NOT NULL , specPrice text NOT NULL , specStock text NOT NULL , specCount text NOT NULL , typeString text NOT NULL , specID text NOT NULL)"];
        if (result) {
            
        }
        else
        {
            
        }
    }
    
}
+ (BOOL)insert:(HasSpecFmdbModel *)model
{
    
    return [_fmdb executeUpdate:@"INSERT INTO t_Spec (userToken,itemID,itemCode,colorSpecID,sizeSpecID,specName,specPrice,specStock,specCount,typeString,specID) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);",model.userToken,model.itemId,model.itemCode,model.colorSpecID,model.sizeSpecID,model.specName,model.specPrice,model.specStock,model.specCount,model.typeString,model.specID ];
}
+ (BOOL)modifySpecCountWithLimit:(HasSpecFmdbModel *)model countText:(NSString *)countText
{
    
    //     NSString *sqlStr=[NSString stringWithFormat:@"SELECT * from t_Spec WHERE userToken = '%@' AND itemID = '%@' AND colorSpecID = '%@' AND sizeSpecID = '%@' AND typeString = '%@' AND specID = '%@';",model.userToken, model.itemId,model.colorSpecID,model.sizeSpecID,model.typeString,model.specID];
    NSString *sqlStr=[NSString stringWithFormat:@"SELECT * from t_Spec WHERE userToken = '%@' AND itemID = '%@' AND typeString = '%@' AND specID = '%@';",model.userToken, model.itemId,model.typeString,model.specID];
    FMResultSet *resultSet = [_fmdb executeQuery:sqlStr];
    if([resultSet next])
    {
        NSInteger idINDEX =(NSInteger) [resultSet objectForColumnName:@"id"];
        NSString * count = [resultSet objectForColumnName:@"specCount"];
        NSString * inv = [resultSet objectForColumnName:@"specStock"];
        NSString *nCount=[NSString stringWithFormat:@"%d",[count intValue]+[countText intValue]];
        if ([nCount intValue]<=[inv intValue]) {
            BOOL res= [_fmdb executeUpdate:@"UPDATE t_Spec SET specCount = ?  WHERE id = ?;",nCount,idINDEX];
            
            if ([nCount intValue]==0) {
                //                NSString *sqlStr=[NSString stringWithFormat:@"DELETE FROM t_Spec WHERE userToken = '%@' AND itemID = '%@' AND colorSpecID = '%@' AND sizeSpecID = '%@' AND typeString = '%@';",model.userToken, model.itemId,model.colorSpecID,model.sizeSpecID,model.typeString];
                
                NSString *sqlStr=[NSString stringWithFormat:@"DELETE FROM t_Spec WHERE userToken = '%@' AND itemID = '%@' AND specID = '%@' AND typeString = '%@';",model.userToken, model.itemId,model.specID,model.typeString];
                [self deleteData:sqlStr];
            }
            
            [resultSet close];
            return  res;
        }
        
        else
        {
            UIAlertView *alertView=[[UIAlertView alloc] initWithTitle:@"提示" message:@"库存不足" delegate:self cancelButtonTitle:nil otherButtonTitles:@"确定", nil];
            [alertView show];
            
            [resultSet close];
            return NO;
        }
    }
    else
    {
        //        return [self insert:model];
        if ([countText intValue]<=[model.specStock intValue])
        {
            [resultSet close];
            return [self insert:model];
        }
        else if ([countText intValue]>[model.specStock intValue]&&[model.specStock intValue]!=0)
        {
            
            UIAlertView *alertView=[[UIAlertView alloc] initWithTitle:@"提示" message:@"库存不足" delegate:self cancelButtonTitle:nil otherButtonTitles:@"确定", nil];
            [alertView show];
            [resultSet close];
            return NO;
        }
        else
        {
            
            UIAlertView *alertView=[[UIAlertView alloc] initWithTitle:@"提示" message:@"库存为0" delegate:self cancelButtonTitle:nil otherButtonTitles:@"确定", nil];
            [alertView show];
            [resultSet close];
            return NO;
        }
    }
}
+ (BOOL)modifySpecCountNoLimit:(HasSpecFmdbModel *)model countText:(NSString *)countText
{
    //    NSString *sqlStr=[NSString stringWithFormat:@"SELECT * from t_Spec WHERE userToken = '%@' AND itemID = '%@' AND colorSpecID = '%@' AND sizeSpecID = '%@' AND typeString = '%@';",model.userToken, model.itemId,model.colorSpecID,model.sizeSpecID,model.typeString];
    NSString *sqlStr=[NSString stringWithFormat:@"SELECT * from t_Spec WHERE userToken = '%@' AND itemID = '%@' AND typeString = '%@' AND specID = '%@';",model.userToken, model.itemId, model.typeString,model.specID];
    FMResultSet *resultSet = [_fmdb executeQuery:sqlStr];
    if([resultSet next])
    {
        NSInteger idINDEX =(NSInteger) [resultSet objectForColumnName:@"id"];
        NSString * count = [resultSet objectForColumnName:@"specCount"];
        NSString *nCount=[NSString stringWithFormat:@"%d",[count intValue]+[countText intValue]];
        
        BOOL res= [_fmdb executeUpdate:@"UPDATE t_Spec SET specCount = ?  WHERE id = ?;",nCount,idINDEX];
        if ([nCount intValue]==0) {
            //            NSString *sqlStr=[NSString stringWithFormat:@"DELETE FROM t_Spec WHERE userToken = '%@' AND itemID = '%@' AND colorSpecID = '%@' AND sizeSpecID = '%@' AND typeString = '%@';",model.userToken, model.itemId,model.colorSpecID,model.sizeSpecID,model.typeString];
            NSString *sqlStr=[NSString stringWithFormat:@"DELETE FROM t_Spec WHERE userToken = '%@' AND itemID = '%@' AND specID = '%@' AND typeString = '%@';",model.userToken, model.itemId,model.specID,model.typeString];
            [self deleteData:sqlStr];
        }
        [resultSet close];
        return res;
    }
    else
    {
        [resultSet close];
        return [self insert:model];
    }
    
}
+ (BOOL)deleteData:(NSString *)deleteSql {
    
    if (deleteSql.length == 0) {
        deleteSql = @"DELETE FROM t_Spec";
    }
    
    return [_fmdb executeUpdate:deleteSql];
    
}

+ (NSArray *)queryData:(NSString *)querySql
{
    if (querySql.length == 0) {
        querySql = @"SELECT * FROM t_Spec;";
    }
    NSMutableArray *dataArray=[NSMutableArray array];
    FMResultSet *resultSet = [_fmdb executeQuery:querySql];
    while ([resultSet next]) {
        
        NSString *userToken = [resultSet stringForColumn:@"userToken"];
        NSString *itemId = [resultSet stringForColumn:@"itemId"];
        NSString *itemCode = [resultSet stringForColumn:@"itemCode"];
        NSString *colorSpecID = [resultSet stringForColumn:@"colorSpecID"];
        NSString *sizeSpecID = [resultSet stringForColumn:@"sizeSpecID"];
        NSString *specName = [resultSet stringForColumn:@"specName"];
        NSString *specPrice = [resultSet stringForColumn:@"specPrice"];
        NSString *specStock = [resultSet stringForColumn:@"specStock"];
        
        NSString *specID = [resultSet stringForColumn:@"specID"];
        NSString *specCount = [resultSet stringForColumn:@"specCount"];
        NSString *typeString = [resultSet stringForColumn:@"typeString"];
        
        
        HasSpecFmdbModel *modal = [HasSpecFmdbModel modalWith:userToken itemId:itemId itemCode:itemCode colorSpecID:colorSpecID sizeSpecID:sizeSpecID specName:specName specPrice:specPrice specStock:specStock specCount:specCount typeString:typeString specID:specID];
        [dataArray addObject:modal];
    }
    [resultSet close];
    return dataArray;
}
+(NSString *)getTotalSpecMoney:(HasSpecFmdbModel *)model
{
    NSString *resultStr=@"";
    NSString *sqlStr=[NSString stringWithFormat:@"SELECT IFNULL(sum(specCount*specPrice),'') as total11 FROM t_Spec WHERE userToken = '%@' AND typeString = '%@' ;" ,model.userToken,model.typeString ];
    FMResultSet *resultSet=[_fmdb executeQuery:sqlStr];
    while (resultSet.next) {
        NSString *TOTAL11=[resultSet objectForColumnName:@"total11"];
       
        resultStr=TOTAL11;
        
    }
    [resultSet close];
    return resultStr;
}

@end
